﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using ZMDTech.SCEntWeb.Web.App_Start;

namespace NOPITest
{
    class Program
    {
        static void Main(string[] args)
        {
            //CreateExcel();//生成Excel
            ReadExcel();//读取Excel
            Console.Read();
        }
        #region NopIde使用

        /// <summary>
        /// 读取Excel数据
        /// </summary>
        public static void ReadExcel()
        {
            var path = @"F:\tel2.xls";//文件地址
            DataTable dt = NPOIHelper.Import(path, 1,2);
            var rows = dt.Rows;
            var clums = dt.Columns;
            foreach (DataRow item in rows)
            {
                var name = item["姓名"].ToString();
                var age = item["年龄"].ToString();
                var sex = item[clums[2]].ToString();
                Console.WriteLine($"{name}\t{age}\t{sex}\t");
             
                ////直接读取
                //foreach (DataColumn colum in clums)
                //{
                //    Console.Write(item[colum].ToString() + "\t");
                //}
                //数据库操作
                //using (var dbset = new Model1())
                //{
                //    try
                //    {
                //        dbset.Configuration.ValidateOnSaveEnabled = false; //不跟踪实体
                //        var model = dbset.YWM_Member_UserInfo.FirstOrDefault(x => !x.IsDeleted && x.LoginAccount == tel);
                //        if (model != null)
                //        {
                //            model.IsSign = true;
                //            model.IsRealName = true;
                //        }
                //        dbset.SaveChanges();
                //    }
                //    catch (Exception e)
                //    {
                //        Console.Write("执行失败");
                //        throw;
                //    }

                //    Console.WriteLine();
                //}

            }
            Console.Write("执行成功！");

        }
        /// <summary>
        /// 生成Excel
        /// </summary>
        public static void CreateExcel()
        {
            //创建工作薄
            var workbook = new HSSFWorkbook();
            //创建表
            var table = workbook.CreateSheet("joye.net");

            //模拟20行20列数据
            var list = new List<string>() { "姓名", "年龄", "性别" };
            for (var i = 0; i < 21; i++)
            {
                var row = table.CreateRow(i);
             
                if (i == 0)//设置表头
                {
                    var index = 0;
                    list.ForEach(x =>
                    {
                        var cell = row.CreateCell(index);
                        cell.SetCellValue(x.ToString());
                        index++;
                    });
                    continue;
                }
                for (int j = 0; j < 3; j++)
                {
                    var cell = row.CreateCell(j);
                    cell.SetCellValue(j);
                }
            }
            //打开xls文件，如没有则创建，如存在则在创建是不要打开该文件
            using (var fs = new FileStream(@"f:/tel2.xls", FileMode.OpenOrCreate))
            {
                workbook.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
                Console.WriteLine("生成成功");
            }
        }
        #endregion
    }
}
